﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using VoteMS.Model;

/* 
 * @author zhu95
 */
namespace VoteMS.DAL
{
    public class ProjectsDAL
    {

        public ProjectsDAL()
        {
        }

        #region 添加

        public int AddProject(ProjectModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" insert into Project (ProjectName,TypeID,ProjectDepart,")
                .Append(" ProjectImage,ProjectInfo,ProjectNumber) ")
                .Append(" values (@ProjectName,@TypeID,@ProjectInfo, ")
                .Append(" @ProjectImage,@ProjectInfo,@ProjectNumber) ");
            SqlParameter[] parameters =
            {
                new SqlParameter("@ProjectName", SqlDbType.VarChar, 200),
                new SqlParameter("@TypeID", SqlDbType.Int, 4),
                new SqlParameter("@ProjectDepart", SqlDbType.VarChar, 50),
                new SqlParameter("@ProjectImage", SqlDbType.VarChar, 50),
                new SqlParameter("@ProjectInfo", SqlDbType.Text),
                new SqlParameter("@ProjectNumber", SqlDbType.Int, 4)
            };
            parameters[0].Value = model.ProjectName;
            parameters[1].Value = model.TypeID;
            parameters[2].Value = model.ProjectDepart;
            parameters[3].Value = model.ProjectImage;
            parameters[4].Value = model.ProjectInfo;
            parameters[5].Value = model.ProjectNumber;
            int number = DBHelper.ExecNoQuery(strSql.ToString(),parameters);
            return number;
        }
        #endregion

        #region

        public int DeleteProjectById(int pid)
        {
            string strsql = " delete from project where projectid=" + pid;
            return DBHelper.ExecNoQuery(strsql);
        }

        #endregion
        #region 修改

        public int UpdateProject(ProjectModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update Project set ProjectName=@ProjectName, TypeID=@TypeID, ")
                .Append(" ProjectDepart=@ProjectDepart,ProjectImage=@ProjectImage,ProjectInfo=@ProjectInfo ")
                .Append(" where ProjectID=@ProjectID; ");
            SqlParameter[] parameters =
            {
                new SqlParameter("@ProjectName", SqlDbType.VarChar, 200),
                new SqlParameter("@TypeID", SqlDbType.Int, 4),
                new SqlParameter("@ProjectDepart", SqlDbType.VarChar, 50),
                new SqlParameter("@ProjectImage", SqlDbType.VarChar, 50),
                new SqlParameter("@ProjectInfo", SqlDbType.Text),
                new SqlParameter("@ProjectID", SqlDbType.Int, 4)
            };
            parameters[0].Value = model.ProjectName;
            parameters[1].Value = model.TypeID;
            parameters[2].Value = model.ProjectDepart;
            parameters[3].Value = model.ProjectImage;
            parameters[4].Value = model.ProjectInfo;
            parameters[5].Value = model.ProjectID;
            int number = DBHelper.ExecNoQuery(strSql.ToString(), parameters);
            return number;
        }

        /// <summary>
        /// 根据项目得票数给该项目得票数+1
        /// </summary>
        /// <param name="projectId">项目编号</param>
        /// <returns></returns>
        public int UpdateProjectNumber(int projectId)
        {
            string sqlstr = "update Project set projectnumber=projectnumber+1 where projectid=" + projectId;
            return DBHelper.ExecNoQuery(sqlstr);
        }

        #endregion

        #region 查询

        public ProjectModel GetProjectById(int projectId)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.Append("select top 1 * from Project where ProjectID=" + projectId);
            SqlDataReader reader = DBHelper.ExecReader(strsql.ToString());
            ProjectModel model = new ProjectModel();
            while (reader.Read())
            {
                model.ProjectID = reader.GetInt32(0);
                model.ProjectName = reader.GetString(1);
                model.TypeID = reader.GetInt32(2);
                model.ProjectDepart = reader.GetString(3);
                model.ProjectImage = reader.GetString(4);
                model.ProjectInfo = reader.GetString(5);
                model.ProjectNumber = reader.GetInt32(6);
            }
            reader.Close();
            reader.Dispose();
            return model;
        }


        /// <summary>
        /// 查询所有项目信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetProjectsAll()
        {
            StringBuilder strsql = new StringBuilder();
            strsql.Append(" select * from Project ");
            DataSet ds = DBHelper.Query(strsql.ToString());
            return ds;
        }

        /// <summary>
        /// 根据条件查询项目信息
        /// </summary>
        /// <param name="sqlWhere"></param>
        /// <returns></returns>
        public DataSet GetProjectByWhere(string sqlWhere)
        {
            //TODO 可改为 inner join
            StringBuilder strsql = new StringBuilder();
            strsql.Append(" select ProjectID,ProjectName,Project.TypeID,TypeName, ")
                .Append(" ProjectDepart,ProjectNumber from Project,ProjectType ")
                .Append(" where Project.TypeID=ProjectType.TypeID ");
            if (!string.IsNullOrEmpty(sqlWhere))
            {
                strsql.Append(" and " + sqlWhere.Trim());
            }
            DataSet ds = DBHelper.Query(strsql.ToString());

            return ds;

        }

        #endregion

    }
}
